/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.ptr.ptrsystem.dao;

import java.util.LinkedList;
import com.ptr.ptrsystem.bean.*;
import com.ptr.controladores.conexionsqlserver;

import java.sql.*;

/**
 *
 * @author pavel
 */
public class Lista {

    public Lista() {
    }
    Vinculo v = new Vinculo();

    public static LinkedList<Vinculo> getlistausuario() {
        LinkedList<Vinculo> lvinculo = new LinkedList<Vinculo>();
        conexionsqlserver b = new conexionsqlserver();
        Connection c = null;
        Statement s = null;
        ResultSet r = null;
        try {
            b.conectar();
            c = b.getconection();
            s = c.createStatement();
            String query = "select * from TB_TIPO_VINCULO";
            r = s.executeQuery(query);
            while (r.next()) {
                Vinculo cb = new Vinculo(r.getString("COD_VINCULO"),
                        r.getString("NOM_VINCULO"),
                        r.getString("DET_VINCULO"));
                lvinculo.add(cb);
            }
            c.close();
            b.cerrarBD();
            s.close();
            r.close();
        } catch (SQLException h) {
            h.printStackTrace();
        }
        return lvinculo;
    }

    public static LinkedList<TipoDocumento> getlistatDocumento() {
        LinkedList<TipoDocumento> ltdocumento = new LinkedList<TipoDocumento>();
        conexionsqlserver b = new conexionsqlserver();
        Connection c = null;
        Statement s = null;
        ResultSet r = null;
        try {
            b.conectar();
            c = b.getconection();
            s = c.createStatement();
            String query = "select * from TB_TIPO_DOCUMENTO";
            r = s.executeQuery(query);
            while (r.next()) {
                TipoDocumento td = new TipoDocumento(r.getString("COD_T_DOC"),
                        r.getString("NOM_T_DOC"));
                ltdocumento.add(td);
            }
            c.close();
            b.cerrarBD();
            s.close();
            r.close();
        } catch (SQLException h) {
            h.printStackTrace();
        }
        return ltdocumento;
    }

    public static LinkedList<Nacionalidad> getlistaNacionalidad() {
        LinkedList<Nacionalidad> lnac = new LinkedList<Nacionalidad>();
        conexionsqlserver b = new conexionsqlserver();
        Connection c = null;
        Statement s = null;
        ResultSet r = null;
        try {
            b.conectar();
            c = b.getconection();
            s = c.createStatement();
            String query = "select * from TB_NACIONALIDAD";
            r = s.executeQuery(query);
            while (r.next()) {
                Nacionalidad tn = new Nacionalidad(r.getString("COD_NACIONALIDAD"),
                        r.getString("NOM_NACIONALIDAD"));
                lnac.add(tn);
            }
            c.close();
            b.cerrarBD();
            s.close();
            r.close();
        } catch (SQLException h) {
            h.printStackTrace();
        }
        return lnac;
    }

    public static LinkedList<TipoDireccion> getlistaTipoDireccion() {
        LinkedList<TipoDireccion> tdir = new LinkedList<TipoDireccion>();
        conexionsqlserver b = new conexionsqlserver();
        Connection c = null;
        Statement s = null;
        ResultSet r = null;
        try {
            b.conectar();
            c = b.getconection();
            s = c.createStatement();
            String query = "select * from TB_TIPO_DIRECCION";
            r = s.executeQuery(query);
            while (r.next()) {
                TipoDireccion td = new TipoDireccion(r.getString("COD_DIR"),
                        r.getString("NOM_TIPO_DIR"));
                tdir.add(td);
            }
            c.close();
            b.cerrarBD();
            s.close();
            r.close();
        } catch (SQLException h) {
            h.printStackTrace();
        }
        return tdir;
    }

    public static LinkedList<TipoEstadoPersona> getlistaEstadoPersona() {
        LinkedList<TipoEstadoPersona> epers = new LinkedList<TipoEstadoPersona>();
        conexionsqlserver b = new conexionsqlserver();
        Connection c = null;
        Statement s = null;
        ResultSet r = null;
        try {
            b.conectar();
            c = b.getconection();
            s = c.createStatement();
            String query = "select * from TB_ESTADO_PERSONA";
            r = s.executeQuery(query);
            while (r.next()) {
                TipoEstadoPersona tep = new TipoEstadoPersona(r.getString("COD_ESTADO_PERS"),
                        r.getString("NOM_ESTADO_PERS"));
                epers.add(tep);
            }
            c.close();
            b.cerrarBD();
            s.close();
            r.close();
        } catch (SQLException h) {
            h.printStackTrace();
        }
        return epers;
    }

    //--------------------------------------------------------------------------------
    public static LinkedList<LugarOcurrencia> getListaLugarOcurr() {
        LinkedList<LugarOcurrencia> LugOcurr = new LinkedList<LugarOcurrencia>();
        conexionsqlserver conn = new conexionsqlserver();
        Connection cn = null;
        Statement st = null;
        ResultSet rs = null;
        try {
            conn.conectar();
            cn = conn.getconection();
            st = cn.createStatement();
            String QUERY = "select * from TB_CENT_LUG_OCURRE";
            rs = st.executeQuery(QUERY);
            while (rs.next()) {
                LugarOcurrencia LOcu = new LugarOcurrencia(rs.getString("COD_LUGAR_OCURR"), rs.getString("NOM_CEN_LUG_OCU"));
                LugOcurr.add(LOcu);
            }
            rs.close();
            st.close();
            cn.close();
            conn.cerrarBD();
        } catch (Exception o) {
            o.printStackTrace();
        }
        return LugOcurr;
    }

    public static LinkedList<Estado> getListaEstado() {
        LinkedList<Estado> LEstado = new LinkedList<Estado>();
        conexionsqlserver conn = new conexionsqlserver();
        Connection cn = null;
        Statement st = null;
        ResultSet rs = null;
        try {
            conn.conectar();
            cn = conn.getconection();
            st = cn.createStatement();
            String QUERY = "select * from TB_ESTADO";
            rs = st.executeQuery(QUERY);
            while (rs.next()) {
                Estado est = new Estado(rs.getString("COD_ESTADO"), rs.getString("NOM_ESTADO"));
                LEstado.add(est);
            }
            rs.close();
            st.close();
            cn.close();
            conn.cerrarBD();
        } catch (Exception es) {
            es.printStackTrace();
        }
        return LEstado;
    }
//////

    public static LinkedList<Nacimiento> getListaNacimiento_NroLibro(String NroLibro) {
        LinkedList<Nacimiento> listaN = new LinkedList<Nacimiento>();
        conexionsqlserver con = new conexionsqlserver();
        Connection cn = null;
        Statement st = null;
        ResultSet rs = null;
        try {
            String Query = "select * from TB_NACIMIENTO where NRO_LIBRO like('%" + NroLibro.trim() + "%')";
            con.conectar();
            cn = con.getconection();
            st = cn.createStatement();
            rs = st.executeQuery(Query);
            while (rs.next()) {
//                Nacimiento BNac = new Nacimiento(rs.getString(1), rs.getString(2), rs.getString(3),
//                        rs.getString(4), rs.getString(5), rs.getString(6), rs.getString(7), rs.getString(8),
//                        rs.getString(9), rs.getString(10), rs.getString(11), rs.getString(12), rs.getString(13),
//                        rs.getString(14), rs.getString(15), rs.getString(16), rs.getString(17), rs.getString(18),
//                        rs.getString(19), rs.getString(20));
//                listaN.add(BNac);
            }
            con.cerrarBD();
            cn.close();
            st.close();
            rs.close();
        } catch (Exception nac) {
            nac.printStackTrace();
        }
        return listaN;
    }
    //

    public static LinkedList<Nacimiento> getListaNacimiento_NroActa(String NroActa) {
        LinkedList<Nacimiento> listaN = new LinkedList<Nacimiento>();
        conexionsqlserver con = new conexionsqlserver();
        Connection cn = null;
        Statement st = null;
        ResultSet rs = null;
        try {
            String Query = "select * from TB_NACIMIENTO where NRO_ACTA_PERS like('%" + NroActa.trim() + "%')";
            con.conectar();
            cn = con.getconection();
            st = cn.createStatement();
            rs = st.executeQuery(Query);
            while (rs.next()) {
//                Nacimiento BNac = new Nacimiento(rs.getString(1), rs.getString(2), rs.getString(3),
//                        rs.getString(4), rs.getString(5), rs.getString(6), rs.getString(7), rs.getString(8),
//                        rs.getString(9), rs.getString(10), rs.getString(11), rs.getString(12), rs.getString(13),
//                        rs.getString(14), rs.getString(15), rs.getString(16), rs.getString(17), rs.getString(18),
//                        rs.getString(19), rs.getString(20));
//                listaN.add(BNac);
            }
            con.cerrarBD();
            cn.close();
            st.close();
            rs.close();
        } catch (Exception nac) {
            nac.printStackTrace();
        }
        return listaN;
    }
    //

    public static LinkedList<Nacimiento> getListaNacimiento_CUI(String CUI) {
        LinkedList<Nacimiento> listaN = new LinkedList<Nacimiento>();
        conexionsqlserver con = new conexionsqlserver();
        Connection cn = null;
        Statement st = null;
        ResultSet rs = null;
        try {
            String Query = "select * from TB_NACIMIENTO where CUI_PERS like('%" + CUI.trim() + "%')";
            con.conectar();
            cn = con.getconection();
            st = cn.createStatement();
            rs = st.executeQuery(Query);
            while (rs.next()) {
//                Nacimiento BNac = new Nacimiento(rs.getString(1), rs.getString(2), rs.getString(3),
//                        rs.getString(4), rs.getString(5), rs.getString(6), rs.getString(7), rs.getString(8),
//                        rs.getString(9), rs.getString(10), rs.getString(11), rs.getString(12), rs.getString(13),
//                        rs.getString(14), rs.getString(15), rs.getString(16), rs.getString(17), rs.getString(18),
//                        rs.getString(19), rs.getString(20));
//                listaN.add(BNac);
            }
            con.cerrarBD();
            cn.close();
            st.close();
            rs.close();
        } catch (Exception nac) {
            nac.printStackTrace();
        }
        return listaN;
    }
    //
    //

    public static LinkedList<Ubigeo> getListaUbigeo() {
        LinkedList<Ubigeo> listaU = new LinkedList<Ubigeo>();
        conexionsqlserver con = new conexionsqlserver();
        Connection cn = null;
        Statement st = null;
        ResultSet rs = null;
        try {
            String Query = "select * from TB_UBIGEO";
            con.conectar();
            cn = con.getconection();
            st = cn.createStatement();
            rs = st.executeQuery(Query);
            while (rs.next()) {
                Ubigeo ubig = new Ubigeo(rs.getString(1), rs.getString(2), rs.getString(3),
                        rs.getString(4), rs.getString(5), rs.getString(6), rs.getString(7), rs.getString(8),
                        rs.getString(9));
                listaU.add(ubig);
            }
            con.cerrarBD();
            cn.close();
            st.close();
            rs.close();
        } catch (Exception nac) {
            nac.printStackTrace();
        }
        return listaU;
    }
    //

    public static LinkedList<Nacimiento> getListaNacimiento_IDUBIGEO(String IdUbigeo) {
        LinkedList<Nacimiento> listaN = new LinkedList<Nacimiento>();
        conexionsqlserver con = new conexionsqlserver();
        Connection cn = null;
        Statement st = null;
        ResultSet rs = null;
        try {
            String Query = "select * from TB_NACIMIENTO where IDUBIGEO like('%" + IdUbigeo.trim() + "%')";
            con.conectar();
            cn = con.getconection();
            st = cn.createStatement();
            rs = st.executeQuery(Query);
            while (rs.next()) {
//                Nacimiento BNac = new Nacimiento(rs.getString(1), rs.getString(2), rs.getString(3),
//                        rs.getString(4), rs.getString(5), rs.getString(6), rs.getString(7), rs.getString(8),
//                        rs.getString(9), rs.getString(10), rs.getString(11), rs.getString(12), rs.getString(13),
//                        rs.getString(14), rs.getString(15), rs.getString(16), rs.getString(17), rs.getString(18),
//                        rs.getString(19), rs.getString(20));
//                listaN.add(BNac);
            }
            con.cerrarBD();
            cn.close();
            st.close();
            rs.close();
        } catch (Exception nac) {
            nac.printStackTrace();
        }
        return listaN;
    }
//    public static LinkedList<Nacimiento> getListaNacimiento(String NroLibro) {
//        LinkedList<Nacimiento> listaN = new LinkedList<Nacimiento>();
//        conexionsqlserver con = new conexionsqlserver();
//        Connection cn = null;
//        Statement st = null;
//        ResultSet rs = null;
//        try {
//            String Query = "select * from TB_NACIMIENTO where NRO_LIBRO_ACTA_NAC like('%"+NroLibro.toUpperCase()+"%')";
//            con.conectar();
//            cn = con.getconection();
//            st = cn.createStatement();
//            rs = st.executeQuery(Query);
//            while (rs.next()) {
//                Nacimiento BNac = new Nacimiento(rs.getString(1), rs.getString(2), rs.getString(3),
//                        rs.getString(4), rs.getString(5), rs.getString(6), rs.getString(7), rs.getString(8),
//                        rs.getString(9), rs.getString(10), rs.getString(11), rs.getString(12), rs.getString(13),
//                        rs.getString(14), rs.getString(15), rs.getString(16), rs.getString(17), rs.getString(18),
//                        rs.getString(19), rs.getString(20));
//                listaN.add(BNac);
//            }
//            con.cerrarBD();
//            cn.close();
//            st.close();
//            rs.close();
//        } catch (Exception nac) {
//            nac.printStackTrace();
//        }
//        return listaN;
//    }
    /////

    public static LinkedList<Defuncion> getListaDefuncion() {
        LinkedList<Defuncion> listaD = new LinkedList<Defuncion>();
        conexionsqlserver con = new conexionsqlserver();
        Connection cn = null;
        Statement st = null;
        ResultSet rs = null;
        try {
            String Query = "select * from TB_DEFUNCION";
            con.conectar();
            cn = con.getconection();
            st = cn.createStatement();
            rs = st.executeQuery(Query);
            while (rs.next()) {
                Defuncion def = new Defuncion(rs.getString(1), rs.getString(2), rs.getString(3), rs.getString(4), rs.getString(5),
                        rs.getString(6), rs.getString(7), rs.getString(8), rs.getString(9), rs.getString(10), rs.getString(11),
                        rs.getString(12), rs.getString(13), rs.getString(14), rs.getString(15));
                listaD.add(def);
            }
            con.cerrarBD();
            cn.close();
            st.close();
            rs.close();
        } catch (Exception de) {
            de.printStackTrace();
        }
        return listaD;
    }

    public static LinkedList<Matrimonio> getListaMatrimonio() {
        LinkedList<Matrimonio> listaM = new LinkedList<Matrimonio>();
        conexionsqlserver con = new conexionsqlserver();
        Connection cn = null;
        Statement st = null;
        ResultSet rs = null;
        try {
            String Query = "select * from TB_MATRIMONIO";
            con.conectar();
            cn = con.getconection();
            st = cn.createStatement();
            rs = st.executeQuery(Query);
            while (rs.next()) {
                Matrimonio mat = new Matrimonio(rs.getString(1), rs.getString(2), rs.getString(3), rs.getString(4), rs.getString(5),
                        rs.getString(6), rs.getString(7), rs.getString(8), rs.getString(9), rs.getString(10), rs.getString(11),
                        rs.getString(12), rs.getString(13), rs.getString(14), rs.getString(15));
                listaM.add(mat);
            }
        } catch (Exception Ma) {
            Ma.printStackTrace();
        }
        return listaM;
    }
    //--------------------------------------------------------------------------------
//
//    public static LinkedList<Matrimonio> getListaMatrimonioXnroActaP(String NAP) {
//        LinkedList<Matrimonio> listaM = new LinkedList<Matrimonio>();
//        conexionsqlserver con = new conexionsqlserver();
//        Connection cn = null;
//        Statement st = null;
//        ResultSet rs = null;
//        try {
//            String Query = "select * from TB_MATRIMONIO where NRO_ACTA_PERS like('%" + NAP + "%')";
//            con.conectar();
//            cn = con.getconection();
//            st = cn.createStatement();
//            rs = st.executeQuery(Query);
//            while (rs.next()) {
//                Matrimonio mat = new Matrimonio(rs.getString(1), rs.getString(2), rs.getString(3), rs.getString(4), rs.getString(5),
//                        rs.getString(6), rs.getString(7), rs.getString(8), rs.getString(9), rs.getString(10), rs.getString(11),
//                        rs.getString(12), rs.getString(13));
//                listaM.add(mat);
//            }
//        } catch (Exception Ma) {
//            Ma.printStackTrace();
//        }
//        return listaM;
//    }
//
//    /////
//    public static LinkedList<Matrimonio> getListaMatrimonioXnroActaM(String NAM) {
//        LinkedList<Matrimonio> listaM = new LinkedList<Matrimonio>();
//        conexionsqlserver con = new conexionsqlserver();
//        Connection cn = null;
//        Statement st = null;
//        ResultSet rs = null;
//        try {
//            String Query = "select * from TB_MATRIMONIO where NRO_ACTA_MAT like('%"+ NAM + "%')";
//            con.conectar();
//            cn = con.getconection();
//            st = cn.createStatement();
//            rs = st.executeQuery(Query);
//            while (rs.next()) {
//                Matrimonio mat = new Matrimonio(rs.getString(1), rs.getString(2), rs.getString(3), rs.getString(4), rs.getString(5),
//                        rs.getString(6), rs.getString(7), rs.getString(8), rs.getString(9), rs.getString(10), rs.getString(11),
//                        rs.getString(12), rs.getString(13));
//                listaM.add(mat);
//            }
//        } catch (Exception Ma) {
//            Ma.printStackTrace();
//        }
//        return listaM;
//    }
//    /////
//    public static LinkedList<Matrimonio> getListaMatrimonioXidUbigeo(String idU) {
//        LinkedList<Matrimonio> listaM = new LinkedList<Matrimonio>();
//        conexionsqlserver con = new conexionsqlserver();
//        Connection cn = null;
//        Statement st = null;
//        ResultSet rs = null;
//        try {
//            String Query = "select * from TB_MATRIMONIO where IDUBIGEO like('%" + idU + "%')";
//            con.conectar();
//            cn = con.getconection();
//            st = cn.createStatement();
//            rs = st.executeQuery(Query);
//            while (rs.next()) {
//                Matrimonio mat = new Matrimonio(rs.getString(1), rs.getString(2), rs.getString(3), rs.getString(4), rs.getString(5),
//                        rs.getString(6), rs.getString(7), rs.getString(8), rs.getString(9), rs.getString(10), rs.getString(11),
//                        rs.getString(12), rs.getString(13));
//                listaM.add(mat);
//            }
//        } catch (Exception Ma) {
//            Ma.printStackTrace();
//        }
//        return listaM;
//    }
//       /////
//    public static LinkedList<Matrimonio> getListaMatrimonioXidUbigeoM(String idUM) {
//        LinkedList<Matrimonio> listaM = new LinkedList<Matrimonio>();
//        conexionsqlserver con = new conexionsqlserver();
//        Connection cn = null;
//        Statement st = null;
//        ResultSet rs = null;
//        try {
//            String Query = "select * from TB_MATRIMONIO where IDUBIGEO_MAT like('%" + idUM + "%')";
//            con.conectar();
//            cn = con.getconection();
//            st = cn.createStatement();
//            rs = st.executeQuery(Query);
//            while (rs.next()) {
//                Matrimonio mat = new Matrimonio(rs.getString(1), rs.getString(2), rs.getString(3), rs.getString(4), rs.getString(5),
//                        rs.getString(6), rs.getString(7), rs.getString(8), rs.getString(9), rs.getString(10), rs.getString(11),
//                        rs.getString(12), rs.getString(13));
//                listaM.add(mat);
//            }
//        } catch (Exception Ma) {
//            Ma.printStackTrace();
//        }
//        return listaM;
//    }
    //--------------------------------------------------------------------------------

    public static String getNombrePersona(String COD_PERSONA) {
        conexionsqlserver con = new conexionsqlserver();
        Connection cn = null;
        Statement st = null;
        ResultSet rs = null;
        String retorno = "";
        try {
            String Query = "select * from TB_PERSONA where COD_PERSONA ='" + COD_PERSONA + "'";
            con.conectar();
            cn = con.getconection();
            st = cn.createStatement();
            rs = st.executeQuery(Query);
            if (rs.next()) {
                retorno = rs.getString("PRIM_APE_PERS") + " " + rs.getString("SEG_APE_PERS") + " - " + rs.getString("PRE_NOM_PERS");
            }
        } catch (Exception Ma) {
            Ma.printStackTrace();
        }
        return retorno;
    }

    public static String getNombreEstado(String CodE) {
        conexionsqlserver con = new conexionsqlserver();
        Connection cn = null;
        Statement st = null;
        ResultSet rs = null;
        String retorno = "";
        try {
            String Query = "select * from TB_ESTADO where COD_ESTADO ='" + CodE + "'";
            con.conectar();
            cn = con.getconection();
            st = cn.createStatement();
            rs = st.executeQuery(Query);
            if (rs.next()) {
                retorno = rs.getString("NOM_ESTADO");
            }
        } catch (Exception Ma) {
            Ma.printStackTrace();
        }
        return retorno;
    }

    public static String getNombreNacionalidad(String CodNacinalidad) {
        conexionsqlserver con = new conexionsqlserver();
        Connection cn = null;
        Statement st = null;
        ResultSet rs = null;
        String retorno = "";
        try {
            String Query = "select * from TB_NACIONALIDAD where COD_NACIONALIDAD ='" + CodNacinalidad + "'";
            con.conectar();
            cn = con.getconection();
            st = cn.createStatement();
            rs = st.executeQuery(Query);
            if (rs.next()) {
                retorno = rs.getString("NOM_NACIONALIDAD");
            }
        } catch (Exception Ma) {
            Ma.printStackTrace();
        }
        return retorno;
    }

    public static String getNombreUbigeo(String IDUBIGEO) {
        conexionsqlserver con = new conexionsqlserver();
        Connection cn = null;
        Statement st = null;
        ResultSet rs = null;
        String retorno = "";
        try {
            String Query = "select * from TB_UBIGEO where IDUBIGEO ='" + IDUBIGEO + "'";
            con.conectar();
            cn = con.getconection();
            st = cn.createStatement();
            rs = st.executeQuery(Query);
            if (rs.next()) {
                retorno = rs.getString("nom_ubigeo_dep") + " " + rs.getString("nom_ubigeo_prov") + " " + rs.getString("nom_ubigeo_dist") + " " + rs.getString("nom_ubigeo_otro");
            }
        } catch (Exception Ma) {
            Ma.printStackTrace();
        }
        return retorno;
    }

    public static LinkedList<CargoPersona> getListaCargoPersona() {
        LinkedList<CargoPersona> listaCP = new LinkedList<CargoPersona>();
        conexionsqlserver con = new conexionsqlserver();
        Connection cn = null;
        Statement st = null;
        ResultSet rs = null;
        try {
            String Query = "select * from TB_CARGO_PERSONA";
            con.conectar();
            cn = con.getconection();
            st = cn.createStatement();
            rs = st.executeQuery(Query);
            while (rs.next()) {
                CargoPersona cp = new CargoPersona(rs.getString(1), rs.getString(2));
                listaCP.add(cp);
            }
        } catch (Exception Ma) {
            Ma.printStackTrace();
        }
        return listaCP;
    }
}
